﻿using HCQ2_Common;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VRDD.InputDto;
using HCQ2_Model.VRDD.InputDto._0427;
using HCQ2_Model.VRDD.InputDto._0531;
using HCQ2_Model.VRDD.InputDto._0629;
using HCQ2_Model.VRDD.OutputDto._0427;
using HCQ2_Model.VRDD.OutputDto._0525;
using HCQ2_Model.VRDD.OutputDto._0629;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class DD_UserDAL: IDD_UserDAL
    {
        /// <summary>
        ///  获取我的信息
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public OutMyInfo GetMyInfo(MyBaseUser param, string user_guid)
        {
            sb?.Clear();
            sb.Append($@"SELECT *,count_star=(SELECT COUNT(0) FROM dbo.T_VLineUserUserStar WHERE star_guid='{user_guid}'),
            count_focus=(SELECT COUNT(0) FROM DD_Fans WHERE fan_id={param.user_id}) FROM 
            (SELECT user_id,user_guid as userid,user_name,buy_gold,give_gold,login_name,user_img,user_phone,CONVERT(varchar(100),user_birth,120) AS user_birth,user_note FROM dbo.T_User with(nolock) WHERE user_id={param.user_id})u INNER JOIN
            (SELECT du_id,du_name,du_sex,du_city,du_city_id,du_hometown,du_hometown_id,du_prof,du_school,count_fan,count_friend,count_visitor FROM DD_User with(nolock) WHERE du_id={param.user_id})d ON u.user_id=d.du_id;");
            return SqlHelper.ExecuteList<OutMyInfo>(sb.ToString()).FirstOrDefault();
        }
        /// <summary>
        ///  获取我附近的用户
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <param name="gps_x">经度</param>
        /// <param name="gps_y">纬度</param>
        /// <returns>附近用户</returns>
        public List<OutNearbyUser> GetUserByMyNearby(QueryNearbyUser param,decimal gps_x,decimal gps_y)
        {
            sb?.Clear();
            sb.Append($@"SELECT * FROM 
            (SELECT TOP {param.count} du_id,gps_x,gps_y from DD_User with(nolock) WHERE gps_x BETWEEN 
            dbo.GetLngE({gps_x},{gps_y},{param.dist}) AND dbo.GetLngW({gps_x},{gps_y},{param.dist}) AND gps_y 
            BETWEEN dbo.GetLatN({gps_y},{param.dist}) AND dbo.GetLatS({gps_y},{param.dist}) AND dbo.GetDistance({gps_x},{gps_y},gps_x,gps_y)<={param.dist})d INNER JOIN
            (SELECT user_id,user_guid as userid,user_name,user_img FROM dbo.T_User with(nolock) WHERE user_status=1)u ON d.du_id=u.user_id ORDER BY d.gps_x,d.gps_y;");
            return SqlHelper.ExecuteList<OutNearbyUser>(sb.ToString());
        }
        /// <summary>
        ///  获取度度圈用户管理列表
        /// </summary>
        /// <param name="param"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<OutputCircleUser> GetCircleList(InputCircleUser param, out int total)
        {
            sb?.Clear();
            string sql = $"SELECT TOP {param.rows} * FROM (SELECT ROW_NUMBER() OVER(ORDER BY l.login_time DESC)AS num, du.*,u.*,l.login_time FROM ";
            sb.Append($@"(SELECT du_id,du_name,du_prof,du_school,user_type,du_hometown,du_sex,address=(province+city+street) FROM dbo.DD_User WHERE 1=1 ");
            //是否VIP
            if (param.user_type > -1)
                sb.Append($" AND user_type={param.user_type} ");
            sb.Append($@")du INNER JOIN (SELECT user_id,user_status,user_name,login_name,user_phone,DATEDIFF(MONTH,user_birth,GETDATE())/12 AS du_birthday,
            CONVERT(varchar(100),create_date,120) AS create_date FROM dbo.T_User WHERE 1=1 ");
            //关键字
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($@" AND (user_name LIKE '%{param.keyword}%' OR login_name LIKE '%{param.keyword}%' OR user_phone LIKE '%{param.keyword}%') ");
            //在线状态
            if (param.user_status > -1)
                sb.Append($" AND user_status={param.user_status } ");
            sb.Append(")u ON du.du_id=u.user_id LEFT JOIN (SELECT user_id,CONVERT(varchar(100),login_time,120) AS login_time FROM dbo.T_Login)l ON u.user_id=l.user_id ");
            total = Helper.ToInt(SqlHelper.ExecuteScalar("SELECT COUNT(0) FROM " + sb.ToString()));
            return SqlHelper.ExecuteList<OutputCircleUser>(sql + sb.ToString() + $")dd WHERE dd.num>{(param.page - 1) * param.rows}");
        }
        /// <summary>
        ///  修改用户类别
        /// </summary>
        /// <param name="user_type"></param>
        /// <param name="userid"></param>
        public void ModifyUserType(int user_type, string userid)
        {
            SqlHelper.ExecuteNonQuery($"UPDATE dbo.DD_User SET user_type={user_type} WHERE du_id IN({userid.Trim(',')});");
        }

        //*************************************0629需求**************************************
        /// <summary>
        ///  获取用户的最新商品
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<ArticleParam> GetArticleParamList(InputUserArticle param)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.size} info.*,df.store_type,store_name=(SELECT folder_name FROM T_DocumentFolder AS tdf WHERE tdf.folder_id=info.store_id)FROM 
            (SELECT file_id,folder_id FROM T_DocumentFolderRelation)rel INNER JOIN
            (SELECT * FROM (
            SELECT folder_id,store_type=1 FROM T_VRFounderApply WHERE USER_ID={param.user_id} AND apply_status=1 AND folder_id NOT IN (SELECT folder_id FROM T_DocumentFolder WHERE founder_id={param.user_id}) UNION
            select folder_id,store_type=0 from T_DocumentFolder where founder_id={param.user_id})f)df ON rel.folder_id=df.folder_id INNER JOIN
            (SELECT file_id,file_name,file_note,store_id,update_time
               FROM T_DocumentInfo AS tdi WHERE if_remove=0 AND file_status=5)info ON rel.[file_id]=info.[file_id] ORDER BY info.update_time DESC;");
            return SqlHelper.ExecuteList<ArticleParam>(sb.ToString());
        }
        /// <summary>
        ///  获取商铺数量
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetArticleParamCount(InputUserArticle param)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM 
            (SELECT folder_id FROM T_VRFounderApply WHERE USER_ID={param.user_id} AND apply_status=1 AND folder_id NOT IN 
            (SELECT folder_id FROM T_DocumentFolder WHERE founder_id={param.user_id})
            UNION SELECT folder_id FROM T_DocumentFolder WHERE founder_id={param.user_id})f;");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取用户动态图片列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<PicList> GetPicList(InputUserArticle param)
        {
            sb?.Clear();
            sb.Append($@"SELECT du.*,p.img_name,p.img_url FROM 
            (SELECT TOP {param.size} id,CONVERT(varchar(100),create_date,120) AS create_date FROM T_VRDDCircleDynamic 
             WHERE du_id={param.user_id} ORDER BY create_date DESC)du LEFT JOIN 
            (SELECT d_id,img_name,img_url FROM T_VRDDCirclePicList)p ON du.id=p.d_id;");
            return SqlHelper.ExecuteList<PicList>(sb.ToString());
        }
        /// <summary>
        ///  获取用户未读动态数量
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetDynamicCount(InputUserArticle param)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0) FROM T_VRDDCircleDynamic WHERE id NOT IN 
            (SELECT d_id FROM T_VRDDCircleReadDynamic WHERE du_id={param.user_id});");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取共同好友列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputMutualFriend> GetMutualFriend(InputMutualFriend param)
        {
            sb?.Clear();
            sb.Append($@"SELECT u.*,d1.*,du.*,sense=dbo.GetDistance({param.gps_x},{param.gps_y},du.gps_x,du.gps_y) FROM 
            (SELECT dd_id_2 AS dd_id,friendNote,friendDescribe,closeType,closeName FROM DD_1 WHERE dd_id_1={param.user_id})d1 INNER JOIN
            (SELECT dd_id_2 AS dd_id FROM DD_1 WHERE dd_id_1={param.du_id})d2 ON d1.dd_id=d2.dd_id INNER JOIN
            (SELECT user_id,user_guid as userid,user_name,user_phone,user_img,user_status,user_note FROM T_User WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (user_name LIKE '%{param.keyword}%' or user_phone LIKE '%{param.keyword}%') ");
            sb.Append($@")u ON d1.dd_id=u.user_id INNER JOIN
            (SELECT du_id,gps_x,gps_y,city,dbo.GetDuName(du_id,{param.user_id},du_name,hide_name) AS du_name,CONVERT(varchar(100),gps_time,120)AS LastLocationTime,user_type FROM DD_User WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND du_name LIKE '%{param.keyword}%' ");
            sb.Append(" )du ON d1.dd_id=du.du_id;");
           return SqlHelper.ExecuteList<OutputMutualFriend>(sb.ToString());
        }
    }
}